﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Net.Mail;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Web.UI.WebControls;

/// <summary>
/// Summary description for connDB
/// </summary>
public class connDB
{
    public connDB()
    {
        //
        // TODO: Add constructor logic here
        //
    }
    private static string ConnectionString
    {

        get
        {
            //return ConfigurationManager.ConnectionStrings["devTmsEPrd"].ConnectionString;
            //return ConfigurationManager.ConnectionStrings["prodTmsEPrd"].ConnectionString;
            return ConfigurationManager.ConnectionStrings["prod"].ConnectionString;
        }
    }
    public void PopulateMajor(DropDownList dd)
    {
        SqlDataReader reader;
        //SqlConnection conn = new SqlConnection(ConnectionString);
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["prodTmsEPrd"].ConnectionString);
        // Both cmd are working
        //SqlCommand cmd = new SqlCommand(" SELECT DISTINCT address_master.state FROM address_master", conn); // working
        //SqlCommand cmd = new SqlCommand("SELECT list_value, list_label FROM dbo.avila_majors order by list_label ", conn);
        // SqlCommand cmd = new SqlCommand("SELECT pcf.PROG_CDE, pdf.prog_desc FROM program_config pcf inner join  program_def pdf on pdf.prog_cde = pcf.prog_cde   where pcf.PROG_CDE not in ( 'NUD' )and pcf.DIV_CDE ='UG' and pdf.SHOW_ON_WEB='Y' and pcf.CANDIDACY_TYPE='F' ORDER BY pdf.prog_desc ASC ", conn);
        SqlCommand cmd = new SqlCommand("CUS_sp_FRM_PopulateMajor", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        try
        {
            conn.Open();
            reader = cmd.ExecuteReader();
            dd.DataSource = reader;
            dd.DataValueField = "PROG_CDE"; //"list_value";
            dd.DataTextField = "prog_desc";//"list_label";
            dd.DataBind();
            reader.Close();

        }
        finally
        {
            conn.Close();

        }

    }

    public void AddInfo(string appguid, string fname, string lname, string pname, string mname,
       string address, string street, string city, string state, string zip,
       string email, string HSName, string GDate, string cphone, string hphone, string ACT, string SAT,
       string Major, string pterm, string stype, string AthInterest, string visitdate, string meet, string hear, string fill, string visitYN, string recruitYN)
    {
        using (SqlConnection conn = new SqlConnection(ConnectionString))
        {

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("CUS_sp_Insert_CampusVisitTransfer", conn);
                //SqlCommand mCount = new SqlCommand("SELECT COUNT(*) FROM sport_personal_info", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@app_guid", SqlDbType.VarChar).Value = appguid;
                cmd.Parameters.Add("@firstName", SqlDbType.VarChar).Value = fname;
                cmd.Parameters.Add("@lastName", SqlDbType.VarChar).Value = lname;
                cmd.Parameters.Add("@PreferName", SqlDbType.VarChar).Value = pname;
                cmd.Parameters.Add("@MiddleName", SqlDbType.VarChar).Value = mname;
                cmd.Parameters.Add("@Address", SqlDbType.VarChar).Value = address;
                cmd.Parameters.Add("@Street", SqlDbType.VarChar).Value = street;
                cmd.Parameters.Add("@State", SqlDbType.VarChar).Value = state;
                cmd.Parameters.Add("@City", SqlDbType.VarChar).Value = city;
                cmd.Parameters.Add("@Zipcode", SqlDbType.VarChar).Value = zip;

                cmd.Parameters.Add("@HomePhone", SqlDbType.VarChar).Value = hphone;
                cmd.Parameters.Add("@MobilePhone", SqlDbType.VarChar).Value = cphone;
                cmd.Parameters.Add("@email", SqlDbType.VarChar).Value = email;
                cmd.Parameters.Add("@School", SqlDbType.VarChar).Value = HSName;
                cmd.Parameters.Add("@HSGradYear", SqlDbType.VarChar, 50).Value = GDate;
                cmd.Parameters.Add("@ACT", SqlDbType.VarChar).Value = ACT;
                cmd.Parameters.Add("@SAT", SqlDbType.VarChar).Value = SAT;
                cmd.Parameters.Add("@IntendedMajor", SqlDbType.VarChar).Value = Major;
                cmd.Parameters.Add("@AthleticInterest", SqlDbType.Text).Value = AthInterest;
                cmd.Parameters.Add("@VisitDateTime", SqlDbType.VarChar).Value = visitdate;
                cmd.Parameters.Add("@ListOthersToMeet", SqlDbType.VarChar, 50).Value = meet;
                cmd.Parameters.Add("@HowDoUHear", SqlDbType.VarChar).Value = hear;




                cmd.Parameters.Add("@WhoFillOut", SqlDbType.VarChar).Value = fill;
                cmd.Parameters.Add("@StudentType", SqlDbType.VarChar).Value = stype;
                cmd.Parameters.Add("@term", SqlDbType.VarChar).Value = pterm;
                cmd.Parameters.Add("@VisitYN", SqlDbType.VarChar).Value = visitYN;
                cmd.Parameters.Add("@RecruitByYN", SqlDbType.VarChar).Value = recruitYN;

                cmd.ExecuteNonQuery();
                // res = mCount.ExecuteScalar().ToString();
                // return res;
            }
            catch (SqlException ex)
            {
                string err = "SQL Exception Caught " + ex.Message.ToString();
                //return err;

            }
            finally
            {

                conn.Close();
            }

        }



    }




    public static void sendMail(string body, string from, string to, string subject)
    {
        string _body = body.ToString();
        // string delim = ";";
        SmtpClient MailObj = new SmtpClient("mail.avila.edu");
        MailMessage msg = new MailMessage();
        msg.From = new MailAddress(from.ToString());
        msg.To.Add(new MailAddress(to.ToString()));
        msg.Subject = subject.ToString();
        msg.Body = _body.ToString();
        msg.IsBodyHtml = true;
        MailObj.Send(msg);
    }
}